var agr = agr || {};

agr.sql = agr.sql || {};

agr.sql.init = [];

agr.sql.init.push('CREATE TABLE Colony ( 	ID INTEGER PRIMARY KEY, 	Name TEXT UNIQUE NOT NULL,     GenderDefault TEXT default "N",     Comment TEXT default "" );');
agr.sql.init.push('INSERT INTO "Colony" VALUES(1,"EPO","N","");');
agr.sql.init.push('CREATE TABLE Genotype (  	ID INTEGER PRIMARY KEY, 	ColonyID INTEGER NOT NULL REFERENCES Colony(ID), 	Name TEXT NOT NULL,      Comment TEXT default "", 	UNIQUE(ColonyID, Name)  );');
agr.sql.init.push('INSERT INTO "Genotype" VALUES(1,1,"Epo fl/fl","");');
agr.sql.init.push('INSERT INTO "Genotype" VALUES(2,1,"C57BL","");');
agr.sql.init.push('CREATE TABLE Mouse ( 	ID INTEGER PRIMARY KEY,     LitterID INTEGER NOT NULL REFERENCES Litter(ID), 	Disabled BOOLEAN DEFAULT 0, 	Gender TEXT default "N", 	GenotypeID INTEGER REFERENCES Genotype(ID),     Cage TEXT default "", 	Tag TEXT default "",     Comment TEXT default "" );');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(1,1,0,"F",1,"","1","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(2,1,0,"F",1,"","2","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(3,1,0,"F",1,"","3","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(4,1,0,"F",1,"","4","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(5,4,0,"F",1,"","5","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(6,4,0,"M",1,"","6","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(7,4,0,"M",1,"","7","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(8,4,0,"M",1,"","8","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(9,4,0,"M",1,"","9","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(10,3,0,"M",1,"","10","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(11,3,0,"M",1,"","11","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(12,3,0,"F",1,"","12","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(13,3,0,"F",1,"","13","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(14,5,0,"F",1,"","14","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(15,5,0,"F",1,"","15","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(16,5,0,"F",1,"","16","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(17,5,0,"F",1,"","17","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(18,5,0,"M",1,"","18","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(19,5,0,"M",1,"","19","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(20,5,0,"M",1,"","20","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(21,2,0,"M",1,"","21","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(22,2,0,"M",1,"","22","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(23,2,0,"M",1,"","23","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(24,2,0,"M",1,"","24","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(25,6,0,"F",1,"","25","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(26,6,0,"F",1,"","26","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(27,6,0,"M",1,"","27","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(28,6,0,"M",1,"","28","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(29,6,0,"M",1,"","29","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(30,9,0,"F",1,"","30","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(31,9,0,"F",1,"","31","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(32,9,0,"F",1,"","32","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(33,9,0,"F",1,"","33","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(34,9,0,"M",1,"","34","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(35,9,0,"M",1,"","35","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(36,9,0,"M",1,"","36","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(37,9,0,"M",1,"","37","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(38,9,0,"M",1,"","38","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(39,15,0,"",1,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(40,15,0,"",1,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(41,15,0,"",1,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(42,15,0,"",1,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(43,7,0,"F",2,"","43","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(44,7,0,"F",2,"","44","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(45,7,0,"F",2,"","45","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(46,7,0,"F",2,"","46","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(47,7,0,"F",2,"","47","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(48,7,0,"F",2,"","48","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(49,7,0,"F",2,"","49","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(50,7,0,"F",2,"","50","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(51,7,0,"F",2,"","51","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(52,7,0,"F",2,"","52","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(53,7,0,"F",2,"","53","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(54,7,0,"M",2,"","54","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(55,7,0,"M",2,"","55","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(56,7,0,"M",2,"","56","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(57,7,0,"M",2,"","57","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(58,7,0,"M",2,"","58","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(59,7,0,"M",2,"","59","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(60,7,0,"M",2,"","60","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(61,7,0,"M",2,"","61","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(62,7,0,"M",2,"","62","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(63,7,0,"M",2,"","63","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(64,7,0,"M",2,"","64","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(65,7,0,"M",2,"","65","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(66,7,0,"M",2,"","66","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(67,7,0,"M",2,"","67","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(68,8,0,"F",2,"","68","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(69,11,0,"F",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(70,11,0,"F",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(71,11,0,"F",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(72,11,0,"F",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(73,11,0,"F",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(74,11,0,"F",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(75,11,0,"F",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(76,11,0,"F",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(77,11,0,"F",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(78,11,0,"M",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(79,11,0,"M",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(80,11,0,"M",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(81,11,0,"M",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(82,11,0,"M",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(83,11,0,"M",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(84,11,0,"M",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(85,11,0,"M",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(86,11,0,"M",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(87,11,0,"M",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(88,11,0,"M",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(89,11,0,"M",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(90,11,0,"M",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(91,14,0,"",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(92,14,0,"",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(93,14,0,"",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(94,14,0,"",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(95,14,0,"",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(96,14,0,"",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(97,14,0,"",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(98,14,0,"",2,"","","");');
agr.sql.init.push('INSERT INTO "Mouse" VALUES(99,14,0,"",2,"","","");');
agr.sql.init.push('CREATE TABLE Experiment ( 	ID INTEGER PRIMARY KEY, 	ColonyID INTEGER NOT NULL REFERENCES Colony(ID), 	Name TEXT NOT NULL, 	Priority INTEGER default 0,     Comment TEXT default "",     DefaultDurationDays integer default 14, 	UNIQUE(ColonyID, Name) );');
agr.sql.init.push('INSERT INTO "Experiment" VALUES(1,1,"Alpha",1,"",14);');
agr.sql.init.push('INSERT INTO "Experiment" VALUES(2,1,"Beta",2,"",14);');
agr.sql.init.push('INSERT INTO "Experiment" VALUES(3,1,"Charlie",3,"",14);');
agr.sql.init.push('CREATE TABLE ExperimentGroup ( 	ID INTEGER PRIMARY KEY, 	ExperimentID INTEGER NOT NULL REFERENCES Experiment(ID), 	Name TEXT NOT NULL, 	GenotypeID INTEGER REFERENCES Genotype(ID), 	MouseQuantity INTEGER NOT NULL DEFAULT 1, 	AgeDaysUpperLimit INTEGER default 0, 	AgeDaysLowerLimit INTEGER default 0,     Gender TEXT default "N",     Comment TEXT default "", 	UNIQUE(ExperimentID,Name) );');
agr.sql.init.push('INSERT INTO "ExperimentGroup" VALUES(1,1,"Alpha 1",1,5,70,42,"M","");');
agr.sql.init.push('INSERT INTO "ExperimentGroup" VALUES(2,1,"Alpha 2",2,5,70,56,"M","");');
agr.sql.init.push('INSERT INTO "ExperimentGroup" VALUES(3,2,"Beta 1",1,5,70,42,"M","");');
agr.sql.init.push('INSERT INTO "ExperimentGroup" VALUES(4,2,"Beta 2",2,5,70,56,"M","");');
agr.sql.init.push('INSERT INTO "ExperimentGroup" VALUES(5,3,"Charlie 1",NULL,5,0,70,"M","");');
agr.sql.init.push('CREATE TABLE Intervention (     ID INTEGER PRIMARY KEY,     ExperimentGroupID integer not null references ExperimentGroup(ID),     MouseID integer not null references Mouse(ID),     StartDate date not null,     EndDate date not null,     Active integer default 0,     Comment text default "",     UNIQUE(MouseID) );');
agr.sql.init.push('CREATE TABLE BreedingPair (     ID INTEGER PRIMARY KEY,     ColonyID INTEGER NOT NULL REFERENCES Colony(ID),     MaleID INTEGER REFERENCES Mouse(ID),     FemaleID INTEGER REFERENCES Mouse(ID),     Comment TEXT default "",     Disabled BOOLEAN default 0,     UNIQUE(MaleID, FemaleID) );');
agr.sql.init.push('CREATE TABLE Litter (     ID INTEGER PRIMARY KEY,     ColonyID INTEGER NOT NULL REFERENCES Colony(ID),     BreedingPairID INTEGER REFERENCES BreedingPair(ID),     DateOfBirth DATE NOT NULL DEFAULT CURRENT_DATE,     Size integer,     Comment TEXT default "" );');
agr.sql.init.push('INSERT INTO "Litter" VALUES(1,1,NULL,"2012-02-22",4,"");');
agr.sql.init.push('INSERT INTO "Litter" VALUES(2,1,NULL,"2012-02-23",4,"");');
agr.sql.init.push('INSERT INTO "Litter" VALUES(3,1,NULL,"2012-03-05",4,"");');
agr.sql.init.push('INSERT INTO "Litter" VALUES(4,1,NULL,"2012-03-28",5,"");');
agr.sql.init.push('INSERT INTO "Litter" VALUES(5,1,NULL,"2012-04-02",7,"");');
agr.sql.init.push('INSERT INTO "Litter" VALUES(6,1,NULL,"2012-04-03",5,"");');
agr.sql.init.push('INSERT INTO "Litter" VALUES(7,1,NULL,"2012-04-22",25,"");');
agr.sql.init.push('INSERT INTO "Litter" VALUES(8,1,NULL,"2012-05-05",1,"");');
agr.sql.init.push('INSERT INTO "Litter" VALUES(9,1,NULL,"2012-05-25",9,"");');
agr.sql.init.push('INSERT INTO "Litter" VALUES(10,1,NULL,"2012-05-28",6,"");');
agr.sql.init.push('INSERT INTO "Litter" VALUES(11,1,NULL,"2012-06-01",22,"");');
agr.sql.init.push('INSERT INTO "Litter" VALUES(12,1,NULL,"2012-06-05",3,"");');
agr.sql.init.push('INSERT INTO "Litter" VALUES(13,1,NULL,"2012-06-07",5,"");');
agr.sql.init.push('INSERT INTO "Litter" VALUES(14,1,NULL,"2012-06-10",9,"");');
agr.sql.init.push('INSERT INTO "Litter" VALUES(15,1,NULL,"2012-06-13",4,"");');
agr.sql.init.push('INSERT INTO "Litter" VALUES(16,1,NULL,"2012-06-15",7,"");');
agr.sql.init.push('CREATE TABLE TeamMember (     ID INTEGER PRIMARY KEY,     ColonyID INTEGER NOT NULL REFERENCES Colony(ID),     Name TEXT,     Email TEXT );');
agr.sql.init.push('CREATE VIEW ColonyView as select     *     ,(select          count(*)     from         Litter     join         Mouse on             Mouse.LitterID = Litter.ID     where         Litter.ColonyID = Colony.ID     ) as MouseCount     ,(select          count(*)     from         Litter     where         Litter.ColonyID = Colony.ID     ) as LitterCount     ,(select         count(*)     from         BreedingPair     where         BreedingPair.ColonyID = Colony.ID     ) as BreedingPairCount     ,(select         count(*)     from         Genotype     where         Genotype.ColonyID = Colony.ID     ) as GenotypeCount     ,(select         count(*)     from         Experiment     where         Experiment.ColonyID = Colony.ID     ) as ExperimentCount from     Colony;');
agr.sql.init.push('CREATE VIEW ExperimentView as select     *     ,(select         count(*)     from         ExperimentGroup     where         ExperimentGroup.ExperimentID = Experiment.ID     ) as ExperimentGroupCount from     Experiment;');
agr.sql.init.push('CREATE VIEW ExperimentGroupView as select     *     ,(select         count(*)     from         Intervention     where         Intervention.ExperimentGroupID = ExperimentGroup.ID     ) as InterventionCount from     ExperimentGroup;');
agr.sql.init.push('CREATE VIEW LitterView as select     *     ,(select         count(*)     from         Mouse     where         Mouse.LitterID = Litter.ID     ) as MouseCount from     Litter;');
agr.sql.init.push('CREATE VIEW BreedingPairView as select     *     ,(select         count(*)     from         Litter     where         Litter.BreedingPairID = BreedingPair.ID     ) as LitterCount from     BreedingPair;');
